package com.mhd.databases.util;

import com.mhd.databases.pojo.TableInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库连接
 *
 * @author dmh96
 */
public class JdbcUtils {

    private final static Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

    /**
     * 驱动信息
     */
    private final static String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";
    private final static String ORACLE_DRIVER = "oracle.jdbc.OracleDriver";

    /**
     * 数据库类型：oracle/mysql
     */
    public static String ORACLE = "Oracle";
    public static String MYSQL = "Mysql";

    /**
     * 查询类型：表/字段
     */
    public static List connectDbToSearch(String DbType, String dbUrl, String username, String password, String sql) {
        Connection conn = null;
        Statement stmt = null;
        List resultLists = new ArrayList<>();

        try {
            if ("MySql".equalsIgnoreCase(DbType)) {
                Class.forName(MYSQL_DRIVER);
            } else {
                Class.forName(ORACLE_DRIVER);
            }
            logger.info("---连接数据库---");
            try {
                conn = DriverManager.getConnection(dbUrl, username, password);

                logger.info("---实例化Statement对象---");
                stmt = conn.createStatement();
                logger.info("---执行sql---");
                ResultSet rs = null;
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    /**
                     * 获取表字段信息
                     */
                    TableInfo tableInfo = new TableInfo();
                    tableInfo.setTableName(rs.getString("tableName"));
                    tableInfo.setBytes(rs.getString("bytes"));
                    tableInfo.setCreateTime(rs.getString("createTime"));
                    tableInfo.setTableComments(rs.getString("tableComments"));
                    tableInfo.setColumnName(rs.getString("columnName"));
                    tableInfo.setColumnType(rs.getString("columType"));
                    tableInfo.setIsNullable(rs.getString("isNullable"));
                    tableInfo.setColumnComments(rs.getString("columComments"));
                    tableInfo.setIsPrimaryKey(rs.getString("isPrimaryKey"));
                    resultLists.add(tableInfo);
                }

                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                logger.info("---连接数据库异常！---");
                e.printStackTrace();
                return resultLists;
            }
        } catch (ClassNotFoundException e) {
            logger.info("---连接数据库找不到类！---");
            e.printStackTrace();
            return resultLists;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    return resultLists;
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    return resultLists;
                }
            }
            logger.info("---数据库连接已关闭---");
            logger.info(resultLists.toString());
        }
        return resultLists;
    }
}
